--SETUP

--DROP TABLE Example
CREATE TABLE Example (
	Id INT NOT NULL PRIMARY KEY CLUSTERED,
	Foo	varchar(50) NULL,
	Bar varchar(50) NULL,
	Bux varchar(50) NULL
)
GO


--we decide later to add an index to Foo due to performance problems
--we "check-in" the following code

CREATE INDEX MyIndex ON Example (Foo);
GO

--but of course this will fail if you try to rerun it
--so we "ask" if it exists, and if not we "tell" SQL Server to create it
IF NOT EXISTS (select * from sys.indexes WHERE name = 'MyIndex' AND object_id = object_id('Example'))
BEGIN
	CREATE INDEX MyIndex ON Example (Foo);
END;

--that's better, but let's say you decide later to change the fillfactor due to page splitting
--we could do this...but then we are ALWAYS rebuilding the index on EVERY deployment, 
--which could be time-consuming
IF EXISTS (select * from sys.indexes WHERE name = 'MyIndex' AND object_id = object_id('Example'))
	DROP INDEX Example.MyIndex
GO
CREATE NONCLUSTERED INDEX [MyIndex] ON [dbo].[Example] ([Foo] ASC) 
WITH (FILLFACTOR = 90) ON [PRIMARY]
--or just use DROP_EXISTING = ON
GO


--So we really need to check for the fill_factor property too.  Quick...do you remember where that is
--in the metadata?

--first, let's assume you didn't change the fill_factor yet.  Let's revert back to "model"
DROP INDEX Example.MyIndex
GO
CREATE INDEX MyIndex ON Example (Foo);
GO

--so we need to check by "property" too
IF NOT EXISTS (select * from sys.indexes WHERE name = 'MyIndex' AND object_id = object_id('Example')
	AND fill_factor = 90)
BEGIN
	PRINT 'Creating'
	CREATE NONCLUSTERED INDEX [MyIndex] ON [dbo].[Example] ([Foo] ASC) 
	WITH (FILLFACTOR = 90, DROP_EXISTING = ON) ON [PRIMARY]
END
GO

--your perf team does some testing and determines you need to Bux and Bar as included columns
--you do this...after you spend 20 minutes thinking through all of the logic
IF NOT EXISTS (
	select * 
	from sys.indexes i
	JOIN sys.index_columns sic
         ON i.object_id = sic.object_id
	JOIN sys.columns sc 
         ON i.object_id = sc.object_id
        AND sic.column_id = sc.column_id 
	WHERE i.name = 'MyIndex' 
	AND i.object_id = object_id('Example')
	AND i.fill_factor = 90
	AND sic.is_included_column = 1
	AND sc.name = 'Bux'
	)
BEGIN
	PRINT 'Creating'
	CREATE NONCLUSTERED INDEX [MyIndex] ON [dbo].[Example] ([Foo] ASC) 
	INCLUDE (Bux)
	WITH (FILLFACTOR = 90, DROP_EXISTING = ON) ON [PRIMARY]
END
GO

--Oh My!  

--But it gets worse!
--Your DBA calls you and says that there was a performance emergency and he added 
--an index on Bar...could you please "check it in to model"
--he ran this...
CREATE INDEX PerfEmergency ON Example(Bar);

--he forgot to tell you the name of the index and you assume he used the 
--Corporate Standard Naming Convention For Indexes (nciTableColumn)
--what happens now?
CREATE INDEX nciExampleBar ON Example(Bar);

--you have a duplicate index...which could make performance (deadlocks) worse
sp_helpindex Example

--we should've renamed it.  